/*
 * @Title JavaJDBC.java
 * @Description
 * @Copyright Copyright(c) 2018
 * @Company com.lizhen
 * @author lizhen
 * @version 0.0.1
 */
package com.lizhen.core.function.jdbc;

import java.sql.*;

/**
 * @author lizhen
 */
public class JDBC {
    /**
     * @author lizhen
     */
    public JDBC() {
    }

    /**
     * @author lizhen
     */
    public static void main(String[] args) {
        MySQL.main(null);
        Oracle.main(null);
        PostgreSQL.main(null);
        Access.main(null);
        SQLServer.main(null);
    }

    interface IConstJDBC {
        // Person info
        public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
        public static final String MYSQL_URL = "jdbc:mysql://localhost/lz?useUnicode=true&characterEncoding=utf-8&useSSL=true";
        public static final String MYSQL_USER = "root";
        public static final String MYSQL_PSWD = "root";
        // Oracle info
        static final String Oracle_DRIVER = "oracle.jdbc.driver.OracleDriver";
        static final String Oracle_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
        static final String Oracle_USER = "lz";
        static final String Oracle_PSWD = "lz";
        // PostgreSQL info
        static final String PostgreSQL_DRIVER = "org.postgresql.Driver";
        static final String POSTGRESQL_URL = "jdbc:postgresql://localhost:5432/lz";
        static final String PostgreSQL_USER = "lz";
        static final String PostgreSQL_PSWD = "lz";
        // Access info
        static final String Access_DRIVER = "com.hxtt.sql.access.AccessDriver";
        static final String Access_URL = "jdbc:Access:///D:/Access/lz.accdb";
        static final String Access_USER = "lz";
        static final String Access_PSWD = "lz";
        // SQLServer info
        static final String SQLServer_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        static final String SQLServer_URL = "jdbc:sqlserver://localhost:1433;DatabaseName=lz";
        static final String SQLServer_USER = "lz";
        static final String SQLServer_PSWD = "lz";
    }

    static class JDBCOperation {
        public static void main() {
        }

        public void query(String dbDriver, String dbUrl, String dbUser, String dbPwsd, String dbSql) {
            Connection conn = null;
            Statement stmt = null;
            try {
                Class.forName(dbDriver);
                System.out.println("Connecting to database...");
                conn = DriverManager.getConnection(dbUrl, dbUser, dbPwsd);
                System.out.println("Creating statement...");
                stmt = conn.createStatement();
                String sql = "SELECT name, age FROM person";
                if (dbSql != null) {
                    sql = dbSql;
                }
                ResultSet rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    System.out.print("Name: " + name);
                    System.out.println(", Age: " + age);
                }
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    static class MySQL {
        public static void main(String[] args) {
            new JDBCOperation().query(IConstJDBC.MYSQL_DRIVER, IConstJDBC.MYSQL_URL, IConstJDBC.MYSQL_USER,
                    IConstJDBC.MYSQL_PSWD, null);
        }
    }

    static class Oracle {
        public static void main(String[] args) {
            new JDBCOperation().query(IConstJDBC.Oracle_DRIVER, IConstJDBC.Oracle_URL, IConstJDBC.Oracle_USER,
                    IConstJDBC.Oracle_PSWD, null);
        }
    }

    static class PostgreSQL {
        public static void main(String[] args) {
            new JDBCOperation().query(IConstJDBC.PostgreSQL_DRIVER, IConstJDBC.POSTGRESQL_URL, IConstJDBC.PostgreSQL_USER,
                    IConstJDBC.PostgreSQL_PSWD, null);
        }
    }

    static class Access {
        public static void main(String[] args) {
            new JDBCOperation().query(IConstJDBC.Access_DRIVER, IConstJDBC.Access_URL, IConstJDBC.Access_USER,
                    IConstJDBC.Access_PSWD, null);
        }
    }

    static class SQLServer {
        public static void main(String[] args) {
            new JDBCOperation().query(IConstJDBC.SQLServer_DRIVER, IConstJDBC.SQLServer_URL, IConstJDBC.SQLServer_USER,
                    IConstJDBC.SQLServer_PSWD, null);
        }
    }

}
